package com.thinkgem.dingdan.common.utils.excel;

import java.awt.image.BufferedImage;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.thinkgem.dingdan.modules.bu.entity.BuOrder;
import com.thinkgem.dingdan.modules.bu.utils.OrderStateEnum;

public class ExportToExcelUtil {

	static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

	public static void out(String httpPriffix, HttpServletResponse response, List<BuOrder> list) throws Exception {
		/**
		 * 指定下载名
		 */
		String fileName = new Date().getTime() + ".xls";
		/**
		 * 编码
		 */
		fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
		/**
		 * 去除空白行
		 */
		response.reset();
		/**
		 * 指定下载的文件名与设置相应头
		 */
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
		/**
		 * 下载文件类型
		 */
		response.setContentType("application/vnd.ms-excel");
		/**
		 * 别给我缓存
		 */
		response.setHeader("Pragma", "no-cache");
		response.setHeader("Cache-Control", "no-cache");
		response.setDateHeader("Expires", 0);
		/**
		 * 得到输出流,放到缓冲区
		 */
		OutputStream output = response.getOutputStream();
		BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);

		/**
		 * 下面是导出的excel格式的设置
		 */
		/******************* 我是分割线 **************************************/

		// 定义单元格报头
		String worksheetTitle = "跟单列表";

		HSSFWorkbook wb = new HSSFWorkbook();
		
		// 创建单元格样式
		HSSFCellStyle cellStyleTitleNumber = wb.createCellStyle();

		HSSFDataFormat formatNumber = wb.createDataFormat();
		// 指定单元格居中对齐
		cellStyleTitleNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTitleNumber.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTitleNumber.setWrapText(true);
		HSSFCellStyle cellStyleNumber = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleNumber.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleNumber.setWrapText(true);

		cellStyleNumber.setDataFormat(formatNumber.getFormat("#,#0"));

		// 创建单元格样式
		HSSFCellStyle cellStyleTitle = wb.createCellStyle();

		HSSFDataFormat format = wb.createDataFormat();
		// 指定单元格居中对齐
		cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTitle.setWrapText(true);
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);

		cellStyle.setDataFormat(format.getFormat("#,#0.00"));

		// 设置单元格字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 100);
		cellStyleTitle.setFont(font);

		// 创建标题列单元格样式
		HSSFCellStyle cellStyleTitleTwo = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleTitleTwo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTitleTwo.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTitle.setWrapText(true);

		HSSFCellStyle cellStyleTwo = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleTwo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTwo.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTwo.setWrapText(true);

		// 设置单元格字体
		HSSFFont fontTwo = wb.createFont();
		fontTwo.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		fontTwo.setFontName("宋体");
		fontTwo.setFontHeightInPoints((short) 10);
		cellStyleTitleTwo.setFont(fontTwo);

		/**************** wo是分割线 *****************************************/

		/**
		 * 工作表列名
		 */
		String picUrl = "图片";
		String productNo = "生产编号";
		String orderDate = "下单日期";
		String shipDate = "发货日期";
		String quanlity = "数量";

		String salesPrice = "销售价格";
		String salesModelPrice = "销售模费";
		String salesTotalPrice = "销售总价";
		String productFee = "工厂单价";
		String productModelFee = "工厂模费";

		String productTotalFee = "工厂总价";
		String profit = "利润";
		String freight = "运费";
		String orderStatus = "订单状态";
		String customerOrderNo = "客户订单编号";

		HSSFSheet sheet = wb.createSheet();
		ExportExcelUtil exportExcel = new ExportExcelUtil(wb, sheet);
		sheet.setDefaultColumnWidth(100);
		sheet.setDefaultRowHeightInPoints(250);

		/**
		 * 创建报表头部
		 */
		exportExcel.createNormalHead(worksheetTitle, 11);
		// 定义第一行
		HSSFRow row1 = sheet.createRow(1);
		HSSFCell cell1 = row1.createCell(0);

		/**
		 * 第一行第一列
		 */
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(picUrl));
		/**
		 * 第一行第二列
		 */
		cell1 = row1.createCell(1);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(productNo));

		/**
		 * 第一行第二列
		 */
		cell1 = row1.createCell(2);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(orderDate));

		/**
		 * 第一行第二列
		 */
		cell1 = row1.createCell(3);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(shipDate));

		/**
		 * 第一行第三列
		 */
		cell1 = row1.createCell(4);
		cell1.setCellStyle(cellStyleTitleNumber);
		cell1.setCellValue(new HSSFRichTextString(quanlity));

		/**
		 * 第一行第四列
		 */
		cell1 = row1.createCell(5);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(salesPrice));

		/**
		 * 第一行第五列
		 */
		cell1 = row1.createCell(6);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(salesModelPrice));

		/**
		 * 第一行第六列
		 */
		cell1 = row1.createCell(7);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(salesTotalPrice));

		/**
		 * 第一行第七列
		 */
		cell1 = row1.createCell(8);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(productFee));

		/**
		 * 第一行第八列
		 */
		cell1 = row1.createCell(9);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(productModelFee));

		/**
		 * 第一行第九列
		 */
		cell1 = row1.createCell(10);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(productTotalFee));

		/**
		 * 第一行第十列
		 */
		cell1 = row1.createCell(11);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(profit));

		/**
		 * 第一行第奎十一列
		 */
		cell1 = row1.createCell(12);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(freight));

		/**
		 * 第一行第十二列
		 */
		cell1 = row1.createCell(13);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(orderStatus));

		/**
		 * 第一行第十三列
		 */
		cell1 = row1.createCell(14);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(customerOrderNo));

		/******************** 我是分割线 *********************************/

		/**
		 * 定义第二行,就是我们的数据
		 */
		HSSFRow row = sheet.createRow(2);
		HSSFCell cell = row.createCell(1);
		/**
		 * 便利我们传进来的user-list
		 */
		BuOrder user = null;
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

		for (int i = 0; i < list.size(); i++) {
			user = list.get(i);
			/**
			 * 从i+2行开始，因为我们之前的表的标题和列的标题已经占用了两行
			 */
			row = sheet.createRow(i + 2);

			short h = 2;// 定义图片所在行
			short l = 0;// 定义图片所在列
			cell = row.createCell(0);
			cell.setCellStyle(cellStyle);
			ByteArrayOutputStream outStream = new ByteArrayOutputStream();
			// 将图片写入流中
			BufferedImage bufferImg = ImageIO.read(getInputStream(httpPriffix + user.getPicUrl()));
			ImageIO.write(bufferImg, "jpg", outStream);
			HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, l, i + h, (short) (l + 1), (i + 1) + h);
			anchor.setAnchorType(0);
			// 利用HSSFPatriarch将图片写入EXCEL
			patriarch.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

			cell = row.createCell(1);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(new HSSFRichTextString(user.getProductNo() + ""));

			cell = row.createCell(2);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(user.getOrderDate());

			cell = row.createCell(3);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(dateFormat.format(user.getShipDate()));

			cell = row.createCell(4);
			cell.setCellStyle(cellStyleTitleNumber);
			cell.setCellValue(Integer.parseInt(user.getQuantity()));

			cell = row.createCell(5);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(Double.parseDouble(user.getSalesPrice()));

			cell = row.createCell(6);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(Double.parseDouble(user.getSalesModelFee()));

			cell = row.createCell(7);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(Double.parseDouble(user.getSalesTotalFee()));

			cell = row.createCell(8);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(Double.parseDouble(user.getProduceFee()));

			cell = row.createCell(9);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(Double.parseDouble(user.getProduceModelFee()));

			cell = row.createCell(10);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(Double.parseDouble(user.getProduceTotalFee()));

			cell = row.createCell(11);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(Double.parseDouble(user.getProfit()));

			cell = row.createCell(12);
			cell.setCellStyle(cellStyle);
			if (StringUtils.isNoneBlank(user.getFreight())) {
				cell.setCellValue(Double.parseDouble(user.getFreight()));
			} else {
				cell.setCellValue(Double.parseDouble("0"));
			}

			cell = row.createCell(13);
			cell.setCellStyle(cellStyle);
			Integer orderState = Integer.parseInt(user.getOrderState());
			if (orderState == OrderStateEnum.C1000.getCode()) {
				cell.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1000.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1001.getCode()) {
				cell.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1001.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1002.getCode()) {
				cell.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1002.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1003.getCode()) {
				cell.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1003.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1004.getCode()) {
				cell.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1004.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1005.getCode()) {
				cell.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1005.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1006.getCode()) {
				cell.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1006.getMsg() + "\n"));
			}
			cell = row.createCell(14);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(user.getCustomerOrderNo());
		}
		try {
			/**
			 * 输出到浏览器
			 */
			bufferedOutPut.flush();
			wb.write(bufferedOutPut);
			bufferedOutPut.close();// 关流
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			list.clear();
		}
	}

	public static void outCs(String httpPriffix, HttpServletResponse response, List<BuOrder> list) throws Exception {

		/**
		 * 指定下载名
		 */
		String fileName = new Date().getTime() + ".xls";
		/**
		 * 编码
		 */
		fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
		/**
		 * 去除空白行
		 */
		response.reset();
		/**
		 * 指定下载的文件名与设置相应头
		 */
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
		/**
		 * 下载文件类型
		 */
		response.setContentType("application/vnd.ms-excel");
		/**
		 * 不给缓存
		 */
		response.setHeader("Pragma", "no-cache");
		response.setHeader("Cache-Control", "no-cache");
		response.setDateHeader("Expires", 0);
		/**
		 * 得到输出流,放到缓冲区
		 */
		OutputStream output = response.getOutputStream();
		BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);

		/**
		 * 下面是导出的excel格式的设置
		 */
		/******************* 我是分割线 **************************************/

		// 定义单元格报头
		String worksheetTitle = "跟单列表";

		HSSFWorkbook wb = new HSSFWorkbook();

		// 创建单元格样式
		HSSFCellStyle cellStyleTitleNumber = wb.createCellStyle();
		HSSFDataFormat formatNumber = wb.createDataFormat();
		// 指定单元格居中对齐
		cellStyleTitleNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTitleNumber.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTitleNumber.setWrapText(true);
		HSSFCellStyle cellStyleNumber = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleNumber.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleNumber.setWrapText(true);
		// 设置单元格字体
		cellStyleNumber.setDataFormat(formatNumber.getFormat("#,#0"));

		// 创建单元格样式
		HSSFCellStyle cellStyleTitle = wb.createCellStyle();
		HSSFDataFormat format = wb.createDataFormat();
		// 指定单元格居中对齐
		cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTitle.setWrapText(true);
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体
		cellStyle.setDataFormat(format.getFormat("#,#0.00"));

		HSSFCellStyle cellStyleFormat = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleFormat.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleFormat.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleFormat.setWrapText(true);
		// 设置单元格字体

		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 100);
		cellStyleTitle.setFont(font);

		// 创建标题列单元格样式
		HSSFCellStyle cellStyleTitleTwo = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleTitleTwo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTitleTwo.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTitle.setWrapText(true);

		HSSFCellStyle cellStyleTwo = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleTwo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTwo.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTwo.setWrapText(true);

		// 设置单元格字体
		HSSFFont fontTwo = wb.createFont();
		fontTwo.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		fontTwo.setFontName("宋体");
		fontTwo.setFontHeightInPoints((short) 10);
		cellStyleTitleTwo.setFont(fontTwo);

		/**************** wo是分割线 *****************************************/

		/**
		 * 工作表列名
		 */
		String picUrl = "图片";
		String productNo = "生产编号";
		String orderDate = "下单日期";
		String shipDate = "出货日期";
		String quanlity = "数量";

		String productFee = "工厂单价";
		String productModelFee = "工厂模费";
		String productTotalFee = "工厂总价";
		String freight = "运费";
		String orderStatus = "订单状态";
		String customerOrderNo = "客户订单编号";

		HSSFSheet sheet = wb.createSheet();
		ExportExcelUtil exportExcel = new ExportExcelUtil(wb, sheet);
		sheet.setDefaultColumnWidth(100);
		sheet.setDefaultRowHeightInPoints(250);
		/**
		 * 创建报表头部
		 */
		exportExcel.createNormalHead(worksheetTitle, 7);
		// 定义第一行
		HSSFRow row1 = sheet.createRow(1);
		HSSFCell cell1 = row1.createCell(0);
		/**
		 * 第一行第一列
		 */
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(picUrl));
		/**
		 * 第一行第二列
		 */
		cell1 = row1.createCell(1);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(productNo));

		/**
		 * 第一行第三列
		 */
		cell1 = row1.createCell(2);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(orderDate));

		/**
		 * 第一行第四列
		 */
		cell1 = row1.createCell(3);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(shipDate));

		/**
		 * 第一行第五列
		 */
		cell1 = row1.createCell(4);
		cell1.setCellStyle(cellStyleTitleNumber);
		cell1.setCellValue(new HSSFRichTextString(quanlity));

		/**
		 * 第一行第六列
		 */
		cell1 = row1.createCell(5);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(productFee));

		/**
		 * 第一行第七列
		 */
		cell1 = row1.createCell(6);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(productModelFee));

		/**
		 * 第一行第八列
		 */
		cell1 = row1.createCell(7);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(productTotalFee));

		/**
		 * 第一行第九列
		 */
		cell1 = row1.createCell(8);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(freight));

		/**
		 * 第一行第十列
		 */
		cell1 = row1.createCell(9);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(orderStatus));

		/**
		 * 第一行第十一列
		 */
		cell1 = row1.createCell(10);
		cell1.setCellStyle(cellStyleTitleTwo);
		cell1.setCellValue(new HSSFRichTextString(customerOrderNo));

		/******************** 我是分割线 *********************************/

		/**
		 * 定义第二行,就是我们的数据
		 */
		HSSFRow row = sheet.createRow(2);
		/**
		 * 便利我们传进来的user-list
		 */
		BuOrder user = null;
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

		for (int i = 0; i < list.size(); i++) {
			user = list.get(i);
			/**
			 * 从i+2行开始，因为我们之前的表的标题和列的标题已经占用了两行
			 */
			row = sheet.createRow(i + 2);

			short h = 2;// 定义图片所在行
			short l = 0;// 定义图片所在列
			HSSFCell cellPic = row.createCell(0);
			cellPic.setCellStyle(cellStyle);
			ByteArrayOutputStream outStream = new ByteArrayOutputStream();
			// 将图片写入流中
			BufferedImage bufferImg = ImageIO.read(getInputStream(httpPriffix + user.getPicUrl()));
			ImageIO.write(bufferImg, "jpg", outStream);
			HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, l, i + h, (short) (l + 1), (i + 1) + h);
			anchor.setAnchorType(0);
			// 利用HSSFPatriarch将图片写入EXCEL
			patriarch.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

			HSSFCell cellProductNo = row.createCell(1);
			cellProductNo.setCellStyle(cellStyle);
			cellProductNo.setCellValue(new HSSFRichTextString(user.getProductNo() + ""));

			HSSFCell cellOrderDate = row.createCell(2);
			cellOrderDate.setCellStyle(cellStyle);
			cellOrderDate.setCellValue(user.getOrderDate());

			HSSFCell cellShipDate = row.createCell(3);
			cellShipDate.setCellStyle(cellStyle);
			cellShipDate.setCellValue(dateFormat.format(user.getShipDate()));

			HSSFCell cellQuantity = row.createCell(4);
			cellQuantity.setCellStyle(cellStyleNumber);
			cellQuantity.setCellValue(Integer.parseInt(user.getQuantity()));

			HSSFCell cellProduceFee = row.createCell(5);
			cellProduceFee.setCellStyle(cellStyle);
			cellProduceFee.setCellValue(Double.parseDouble(user.getProduceFee()));

			HSSFCell cellProduceModelFee = row.createCell(6);
			cellProduceModelFee.setCellStyle(cellStyle);
			cellProduceModelFee.setCellValue(Double.parseDouble(user.getProduceModelFee()));

			HSSFCell cellProduceTotalFee = row.createCell(7);
			cellProduceTotalFee.setCellStyle(cellStyle);
			cellProduceTotalFee.setCellValue(Double.parseDouble(user.getProduceTotalFee() + "\n"));

			HSSFCell cellFreight = row.createCell(8);
			cellFreight.setCellStyle(cellStyle);
			if (StringUtils.isNoneBlank(user.getFreight())) {
				cellFreight.setCellValue(Double.parseDouble(user.getFreight()));
			} else {
				cellFreight.setCellValue(Double.parseDouble("0"));
			}

			HSSFCell cellOrderState = row.createCell(9);
			cellOrderState.setCellStyle(cellStyle);
			Integer orderState = Integer.parseInt(user.getOrderState());
			if (orderState == OrderStateEnum.C1000.getCode()) {
				cellOrderState.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1000.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1001.getCode()) {
				cellOrderState.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1001.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1002.getCode()) {
				cellOrderState.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1002.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1003.getCode()) {
				cellOrderState.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1003.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1004.getCode()) {
				cellOrderState.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1004.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1005.getCode()) {
				cellOrderState.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1005.getMsg() + "\n"));
			} else if (orderState == OrderStateEnum.C1006.getCode()) {
				cellOrderState.setCellValue(new HSSFRichTextString("\n" + OrderStateEnum.C1006.getMsg() + "\n"));
			}

			HSSFCell cellCustomerOrderNo = row.createCell(10);
			cellCustomerOrderNo.setCellStyle(cellStyle);
			cellCustomerOrderNo.setCellValue(user.getCustomerOrderNo());

		}
		try {
			/**
			 * 输出到浏览器
			 */
			bufferedOutPut.flush();
			wb.write(bufferedOutPut);
			bufferedOutPut.close();// 关流
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			list.clear();
		}
	}

	public static InputStream getInputStream(String path) {
		InputStream inputStream = null;
		HttpURLConnection httpURLConnection = null;
		try {
			URL url = new URL(path);
			if (url != null) {
				httpURLConnection = (HttpURLConnection) url.openConnection();
				httpURLConnection.setConnectTimeout(3000);
				httpURLConnection.setRequestMethod("GET");
				int responseCode = httpURLConnection.getResponseCode();
				if (responseCode == 200) {
					inputStream = httpURLConnection.getInputStream();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return inputStream;
	}

}
